package app.web.cartoon.search;

import cn.lily.com.component.widget.standard.TWForm;
import cn.lily.com.db.DBFactory;
import cn.lily.com.db.container.reader.DataTable;
import cn.lily.com.db.sql.manager.DBFastManager;
import cn.lily.com.exception.YaconException;
import cn.lily.com.file.FilePath;
import cn.lily.com.file.io.FileOutputManager;
import cn.lily.esb.enums.EnumManager;
import cn.lily.web.page.BusinessPage;
import cn.lily.web.widget.sql.TSqlGrid;
import cn.lily.web.widget.standard.TButton;
import cn.lily.web.widget.standard.TButtonPilot;
import cn.lily.web.widget.standard.TDrop;
import cn.lily.web.widget.standard.TInput;
/**
 * 动漫汇总
 * @author JiaJ
 * @date 2017年12月7日
 *
 */
public class CartoonSearch extends BusinessPage { 

	 public void page_onLoad() throws YaconException {

		 TSqlGrid cartoonAllGrid = sqlGrid("cartoonAllGrid");
		 cartoonAllGrid.setDictTable("LEO_CAN_REPORT_ALL");

		 cartoonAllGrid.setEdit(true);
		 cartoonAllGrid.setExcuteMethod("save");
		 cartoonAllGrid.setRefreshMethod("refresh");

		 cartoonAllGrid.createFieldTitle("CAN_NAME").setDataAlign("center").setFieldAlign("center");
		 cartoonAllGrid.createFieldTitle("CAN_DETAIL_NAME").setDataAlign("center").setFieldAlign("center");
		 cartoonAllGrid.createFieldTitle("CAN_TYP_MEMO").setDataAlign("center").setFieldAlign("center");
		 cartoonAllGrid.createFieldTitle("CAN_QUA").setFieldWidth("70px").setDataAlign("center").setFieldAlign("center");
		 cartoonAllGrid.createFieldTitle("CAN_AREA").setFieldWidth("70px").setDataAlign("center").setFieldAlign("center");
		 cartoonAllGrid.createFieldTitle("CAN_SOURCE").setFieldWidth("70px").setDataAlign("center").setFieldAlign("center");
		 cartoonAllGrid.createFieldTitle("CAN_STAT").setFieldWidth("70px").setDataAlign("center").setFieldAlign("center");		 
		 cartoonAllGrid.createFieldTitle("CAN_KIND").setFieldWidth("70px").setDataAlign("center").setFieldAlign("center");
		 cartoonAllGrid.createFieldTitle("CAN_TOTAL").setFieldWidth("70px").setDataAlign("center").setFieldAlign("center");
		 cartoonAllGrid.createFieldTitle("CAN_TIME").setDataAlign("center").setFieldAlign("center");
		 cartoonAllGrid.createFieldTitle("RES_STORE_SIZE").setFieldWidth("100px").setDataAlign("center").setFieldAlign("center");
		 cartoonAllGrid.createFieldTitle("DISK_ID").setFieldWidth("70px").setDataAlign("center").setFieldAlign("center");
		 cartoonAllGrid.createFieldTitle("RES_STORE_PLACE").setFieldWidth("400px").setDataAlign("center").setFieldAlign("center");
		 
		 cartoonAllGrid.createFieldTitle("CAN_FIRM_MEMO").setFieldVisible(false);		 		 
		 cartoonAllGrid.createFieldTitle("CAN_NAM_ENG").setFieldVisible(false);
		 cartoonAllGrid.createFieldTitle("CAN_NAM_JAP").setFieldVisible(false);
		 cartoonAllGrid.createFieldTitle("CAN_SIZE").setFieldVisible(false);
		 cartoonAllGrid.createFieldTitle("CAN_ID").setFieldVisible(false);	 
		 cartoonAllGrid.createFieldTitle("CAN_NAME_MEMO").setFieldVisible(false);
		 cartoonAllGrid.createFieldTitle("CAN_NAME_OTHER").setFieldVisible(false);
		 cartoonAllGrid.createFieldTitle("CAN_FIRST").setFieldVisible(false);
		 cartoonAllGrid.createFieldTitle("RES_TAG_MEMO").setFieldVisible(false);
		 cartoonAllGrid.createFieldTitle("CAN_YEAR").setFieldVisible(false);
		 cartoonAllGrid.createFieldTitle("CAN_MONTH").setFieldVisible(false);
		 cartoonAllGrid.createFieldTitle("CAN_FANS").setFieldVisible(false);
		 cartoonAllGrid.createFieldTitle("RES_ID").setFieldVisible(false);
		 cartoonAllGrid.createFieldTitle("CAN_DETAIL_ID").setFieldVisible(false);
		 
		 this.setFindForm(cartoonAllGrid.createFindForm());
		 
		 cartoonAllGrid.detailPage().setPagePath("cartoonSearchEdit.jsp");

		 TButtonPilot cartoonAllPilot = ButtonPilot("cartoonAllPilot");
		 cartoonAllPilot.setPilotIco("/widget/buttonPilot.gif");
		 cartoonAllPilot.setPilotText("动漫汇总统计表信息");
		 
		 TButton updateBtn = Button("updateBtn");
		 updateBtn.setIcoPath("glyphicon glyphicon-refresh");
		 updateBtn.setViewClick("updateBtn_onclick");
		 updateBtn.setLabel("数据更新");
		 
		 cartoonAllPilot.addButtons(updateBtn);
		 
		 TButton clearBtn = Button("clearBtn");
		 clearBtn.setIcoPath("glyphicon glyphicon-trash");
		 clearBtn.setViewClick("clearBtn_onclick");
		 clearBtn.setLabel("数据清空");
		 
		 cartoonAllPilot.addButtons(clearBtn);

		 cartoonAllGrid.setButtonPilot(cartoonAllPilot);
		 
		 cartoonAllGrid.setTopButtonType("all");
		 
		 getPage().addElement(cartoonAllGrid);

	 }

	 public void refresh() throws YaconException {

		 TSqlGrid cartoonAllGrid = sqlGrid("cartoonAllGrid");
		 String sqlString = "select DISK_ID,to_char(CAN_TIME,'yyyy-mm-dd hh24:mi:ss') CAN_TIME,CAN_TOTAL,CAN_FIRM_MEMO,CAN_TYP_MEMO,CAN_NAME,CAN_NAM_ENG,CAN_NAM_JAP,CAN_SIZE,CAN_ID,CAN_DETAIL_NAME,CAN_NAME_MEMO,CAN_NAME_OTHER,CAN_FIRST,RES_TAG_MEMO,CAN_YEAR,CAN_MONTH,CAN_FANS,RES_ID,CAN_DETAIL_ID,CAN_STAT,CAN_SOURCE,CAN_AREA,CAN_QUA,CAN_KIND,RES_STORE_SIZE,RES_STORE_PLACE "
		 		          + "from ${schema}.LEO_CAN_REPORT_ALL ";
		 
		 sqlString += this.setWhereSQL();
		 
		 cartoonAllGrid.setCommSqlStr(sqlString);
		 this.setSelfMessage(cartoonAllGrid.getDataElement());

	 }

	/**
	 * 查询表单
	 * @throws YaconException
	 */
	private void setFindForm(TWForm findForm) {
	
		//动漫名称
		TInput cartoonNam = Input("cartoonNam");
		cartoonNam.setLabel("动漫名称");
		
		//动漫清晰度
		TDrop cartoonQua = Drop("cartoonQua");
		cartoonQua.put("", "");
		cartoonQua.setEnumDetail(EnumManager.getTableEnum("CAN_QUA"));	
		cartoonQua.setLabel("动漫清晰度");
		
		//动漫状态
		TDrop cartoonStat = Drop("cartoonStat");	
		cartoonStat.put("", "");
		cartoonStat.setEnumDetail(EnumManager.getTableEnum("CAN_STAT"));		
		cartoonStat.setLabel("动漫状态");
		
		//动漫首字母
		TDrop cartoonFirst = Drop("cartoonFirst");		
		cartoonFirst.put("", "");
		cartoonFirst.setEnumDetail(EnumManager.getTableEnum("CAN_FIRST"));		
		cartoonFirst.setLabel("动漫首字母");
		
		//动漫地区
		TDrop cartoonArea = Drop("cartoonArea");	
		cartoonArea.put("", "");
		cartoonArea.setEnumDetail(EnumManager.getTableEnum("CAN_AREA"));		
		cartoonArea.setLabel("动漫地区");
		
		//动漫来源
		TDrop cartoonSrc = Drop("cartoonSrc");		
		cartoonSrc.put("", "");
		cartoonSrc.setEnumDetail(EnumManager.getTableEnum("CAN_SOURCE"));		
		cartoonSrc.setLabel("动漫来源");
		
		//动漫种类
		TDrop cartoonKind = Drop("cartoonKind");
		cartoonKind.put("", "");
		cartoonKind.setEnumDetail(EnumManager.getTableEnum("CAN_KIND"));	
		cartoonKind.setLabel("动漫种类");
		
		//动漫磁盘
		TDrop cartoonDisk = Drop("cartoonDisk");
		cartoonDisk.put("", "");
		cartoonDisk.setEnumDetail(EnumManager.getDBEnum("LEO_DISK_INFO", "DISK_ID", "DISK_NAM"));
		cartoonDisk.setLabel("动漫磁盘");
		
		//动漫集数
		TInput cartoonNum = Input("cartoonNum");
		cartoonNum.setLabel("动漫集数");
		
		//动漫标签
		TInput cartoonTag = Input("cartoonTag");
		cartoonTag.setLabel("动漫标签");
		
		//动漫厂商
		TInput cartoonFirm = Input("cartoonFirm");
		cartoonFirm.setLabel("动漫厂商");
		
		//年份
		TDrop cartoonYear = Drop("cartoonYear");
		cartoonYear.put("", "");
		cartoonYear.setEnumDetail(EnumManager.getSqlEnum("select distinct CAN_YEAR from LEO_CAN_TIME order by CAN_YEAR DESC", "CAN_YEAR", "CAN_YEAR"));
		cartoonYear.setLabel("年份");
		
		//月份
		TDrop cartoonMonth = Drop("cartoonMonth");
		cartoonMonth.put("", "");
		cartoonMonth.setEnumDetail(EnumManager.getSqlEnum("select distinct CAN_MONTH from LEO_CAN_TIME order by CAN_MONTH DESC", "CAN_MONTH", "CAN_MONTH"));
		cartoonMonth.setLabel("月份");
		
		//番数
		TDrop cartoonFans = Drop("cartoonFans");
		cartoonFans.put("", "");
		cartoonFans.setEnumDetail(EnumManager.getSqlEnum("select distinct CAN_FANS from LEO_CAN_TIME order by CAN_FANS DESC", "CAN_FANS", "CAN_FANS"));
		cartoonFans.setLabel("番数");
		
		//查询
		TButton find = Button("selectBtn");
		find.setIcoPath("/button/find.gif");
		find.setViewClick("selectBtn_onclick");
		find.setLabel("查询");
		
		//重置
		TButton reset = Button("resetBtn");
		reset.setIcoPath("/button/cancle.gif");
		reset.setViewClick("resetBtn_onclick");
		reset.setLabel("重置");
		
		//Form排版
		findForm.createFormCell(cartoonNam,4,"center");
		findForm.createFormCell(cartoonQua,4,"center");
		findForm.createFormCell(cartoonStat,4,"center");
		findForm.createFormRow();
		findForm.createFormCell(cartoonFirst,4,"center");		
		findForm.createFormCell(cartoonArea,4,"center");
		findForm.createFormCell(cartoonSrc,4,"center");
		findForm.createFormRow();
		findForm.createFormCell(cartoonKind,4,"center");
		findForm.createFormCell(cartoonDisk,4,"center");
		findForm.createFormCell(cartoonNum,4,"center");
		findForm.createFormRow();
		
		findForm.createFormCell(cartoonTag,4,"center");
		findForm.createFormCell(cartoonFirm,4,"center");
		findForm.createFormCell(cartoonFans,4,"center");
		findForm.createFormRow();
		
		findForm.createFormCell(cartoonYear,5,"center");		
		findForm.createFormCell(cartoonMonth,5,"center");
		
		findForm.createFormCell(find,2,"center");
		findForm.addFormCell(reset);
		
	}
	
	/**
	 * 获取条件语句（根据实际情况调整）
	 */
	public String setWhereSQL() {
		
		String ret = " where 1=1 ";

		String cartoonNam = this.getViewTable().getAttributeValue("cartoonNam");
		String cartoonQua = this.getViewTable().getAttributeValue("cartoonQua");
		String cartoonStat = this.getViewTable().getAttributeValue("cartoonStat");
		String cartoonFirst = this.getViewTable().getAttributeValue("cartoonFirst");
		String cartoonArea = this.getViewTable().getAttributeValue("cartoonArea");
		String cartoonSrc = this.getViewTable().getAttributeValue("cartoonSrc");
		String cartoonKind = this.getViewTable().getAttributeValue("cartoonKind");
		String cartoonDisk = this.getViewTable().getAttributeValue("cartoonDisk");
		String cartoonNum = this.getViewTable().getAttributeValue("cartoonNum");
		String cartoonTag = this.getViewTable().getAttributeValue("cartoonTag");
		String cartoonFirm = this.getViewTable().getAttributeValue("cartoonFirm");
		String cartoonYear = this.getViewTable().getAttributeValue("cartoonYear");
		String cartoonMonth = this.getViewTable().getAttributeValue("cartoonMonth");
		String cartoonFans = this.getViewTable().getAttributeValue("cartoonFans");

		if(cartoonNam!=null&&!"".equals(cartoonNam)){
			ret += " and CAN_NAME_MEMO like '%"+cartoonNam+"%' ";
		}
		if(cartoonQua!=null&&!"".equals(cartoonQua)){
			ret += " and CAN_QUA = '"+cartoonQua+"' ";
		}
		if(cartoonStat!=null&&!"".equals(cartoonStat)){
			ret += " and CAN_STAT = '"+cartoonStat+"' ";
		}
		if(cartoonFirst!=null&&!"".equals(cartoonFirst)){
			ret += " and CAN_FIRST = '"+cartoonFirst+"' ";
		}
		if(cartoonArea!=null&&!"".equals(cartoonArea)){
			ret += " and CAN_AREA = '"+cartoonArea+"' ";
		}
		if(cartoonSrc!=null&&!"".equals(cartoonSrc)){
			ret += " and CAN_SOURCE = '"+cartoonSrc+"' ";
		}
		if(cartoonKind!=null&&!"".equals(cartoonKind)){
			ret += " and CAN_KIND = '"+cartoonKind+"' ";
		}
		if(cartoonDisk!=null&&!"".equals(cartoonDisk)){
			ret += " and DISK_ID = '"+cartoonDisk+"' ";
		}
		if(cartoonNum!=null&&!"".equals(cartoonNum)){
			ret += " and CAN_TOTAL = '%"+cartoonNum+"%' ";
		}
		if(cartoonTag!=null&&!"".equals(cartoonTag)){
			ret += " and RES_TAG_MEMO like '%"+cartoonTag+"%' ";
		}
		if(cartoonFirm!=null&&!"".equals(cartoonFirm)){
			ret += " and CAN_FIRM_MEMO like '%"+cartoonFirm+"%' ";
		}
		if(cartoonYear!=null&&!"".equals(cartoonYear)){
			ret += " and CAN_YEAR = '"+cartoonYear+"' ";
		}
		if(cartoonMonth!=null&&!"".equals(cartoonMonth)){
			ret += " and CAN_MONTH = '"+cartoonMonth+"' ";
		}
		if(cartoonFans!=null&&!"".equals(cartoonFans)){
			ret += " and CAN_FANS = '"+cartoonFans+"' ";
		}

		return ret;
	}
	
	/**
	 * 清空数据
	 */
	public void clear(){
		DBFactory.getActionDB().executeUpdate("truncate table LEO_CAN_REPORT_ALL");
		DBFactory.getActionDB().commit();
		this.setSuccess("数据清空成功！");
	}
	
	public void update(){
		
		try {
			
			String querySql = "select detail.RES_ID,detail.CAN_DETAIL_ID,detail.CAN_ID,detail.CAN_DETAIL_NAME,detail.CAN_QUA,detail.CAN_KIND,detail.CAN_STAT,detail.CAN_TOTAL,to_char(detail.CAN_TIME,'yyyy-mm-dd hh24:mi:ss') CAN_TIME,detail.CAN_FIRM_MEMO,detail.CAN_TYP_MEMO,"
					+ "cartoon.CAN_NAME,cartoon.CAN_NAM_JAP,cartoon.CAN_NAM_ENG,cartoon.CAN_AREA,cartoon.CAN_SOURCE,cartoon.CAN_DEMO,cartoon.CAN_SIZE,cartoon.CAN_NAME_MEMO,cartoon.CAN_NAME_OTHER,cartoon.CAN_FIRST,"
					+ "time.CAN_YEAR,time.CAN_MONTH,time.CAN_FANS,"
					+ "res.RES_NAM,res.RES_TYP,res.RES_STORE_SIZE,res.RES_STORE_PLACE,res.RES_OPER,res.RES_MEMO,res.DISK_ID,res.RES_TAG_MEMO "
					+ "from LEO_CAN_DETAIL_INFO detail "
					+ "left join LEO_CAN_INFO cartoon on detail.CAN_ID=cartoon.CAN_ID "
					+ "left join LEO_CAN_TIME time on detail.CAN_DETAIL_ID=time.CAN_DETAIL_ID "
					+ "left join LEO_RES_INFO res on detail.RES_ID=res.RES_ID ";
			
			DataTable resultTable = DBFactory.getActionDB().executeQueryTable(querySql);
			
			DBFactory.getActionDB().executeUpdate("truncate table LEO_CAN_REPORT_ALL");
			DBFactory.getActionDB().commit();
			
			String insertSql = "insert into LEO_CAN_REPORT_ALL "
					         + "(DISK_ID,CAN_DEMO,CAN_TIME,CAN_TOTAL,CAN_FIRM_MEMO,CAN_TYP_MEMO,CAN_NAME,CAN_NAM_ENG,CAN_NAM_JAP,"
					         + "CAN_SIZE,CAN_ID,CAN_DETAIL_NAME,CAN_NAME_MEMO,CAN_NAME_OTHER,"
					         + "CAN_FIRST,RES_TAG_MEMO,CAN_YEAR,CAN_MONTH,CAN_FANS,RES_ID,CAN_DETAIL_ID,CAN_STAT,CAN_SOURCE,CAN_AREA,"
					         + "CAN_QUA,CAN_KIND,RES_STORE_SIZE,RES_STORE_PLACE) "
					         + "values "
					         + "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			
			DBFastManager fastManager = new DBFastManager();
				
			fastManager.setAutoError(true); 

			fastManager.setAutoErrorFast(true); 
			
			fastManager.setErrorDeep(10); //纠错深度
			fastManager.setLimitNum(2); //设置递归次数
			fastManager.beginTrans(500); //开启事务，提交数 500
			
			//设置预置 SQL
			fastManager.setCommonSql(insertSql);
			while(resultTable.next()){
				
				fastManager.begin();
			
				fastManager.setString(resultTable.getString("DISK_ID"));
				fastManager.setString(resultTable.getString("CAN_DEMO"));
				fastManager.setTimesTamp(resultTable.getString("CAN_TIME"), "datetime");
				fastManager.setString(resultTable.getString("CAN_TOTAL"));
				fastManager.setString(resultTable.getString("CAN_FIRM_MEMO"));
				fastManager.setString(resultTable.getString("CAN_TYP_MEMO"));
				fastManager.setString(resultTable.getString("CAN_NAME"));
				fastManager.setString(resultTable.getString("CAN_NAM_ENG"));
				fastManager.setString(resultTable.getString("CAN_NAM_JAP"));
				fastManager.setString(resultTable.getString("CAN_SIZE"));
				fastManager.setString(resultTable.getString("CAN_ID"));
				fastManager.setString(resultTable.getString("CAN_DETAIL_NAME"));
				fastManager.setString(resultTable.getString("CAN_NAME_MEMO")+","+resultTable.getString("CAN_NAME_OTHER")+","+resultTable.getString("CAN_NAME")+","+resultTable.getString("CAN_DETAIL_NAME"));
				fastManager.setString(resultTable.getString("CAN_NAME_OTHER"));
				fastManager.setString(resultTable.getString("CAN_FIRST"));
				fastManager.setString(resultTable.getString("RES_TAG_MEMO"));
				fastManager.setString(resultTable.getString("CAN_YEAR"));
				fastManager.setString(resultTable.getString("CAN_MONTH"));
				fastManager.setString(resultTable.getString("CAN_FANS"));
				fastManager.setString(resultTable.getString("RES_ID"));
				fastManager.setString(resultTable.getString("CAN_DETAIL_ID"));
				fastManager.setString(resultTable.getString("CAN_STAT"));
				fastManager.setString(resultTable.getString("CAN_SOURCE"));
				fastManager.setString(resultTable.getString("CAN_AREA"));
				fastManager.setString(resultTable.getString("CAN_QUA"));
				fastManager.setString(resultTable.getString("CAN_KIND"));
				fastManager.setString(resultTable.getString("RES_STORE_SIZE"));
				fastManager.setString(resultTable.getString("RES_STORE_PLACE"));
					
				fastManager.fastInsert();
				fastManager.end();
				
			}
			//最后提交
			fastManager.commit();
			
			//获取所有异常 SQL – 这里使用一个文件流输出了			
			if(fastManager.getAllErrorSql().size()>0){
				FileOutputManager errorFOM = new FileOutputManager();
				errorFOM.loadFile(FilePath.getWebRootPath("/file/error/sql/"+System.currentTimeMillis()+".sql"));
				for(String sql:fastManager.getAllErrorSql()){
					System.out.println(sql);
					errorFOM.writeLine(sql);
				}
				errorFOM.free();
			}
				
			//释放所有连接 
			fastManager.free();
							
			this.setSuccess("更新成功！");
			
		} catch (Exception e) {
			e.printStackTrace();
			this.setError("更新失败");
		} 
		
	}


}
